﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;

namespace FWMS.DAO
{
    class KucunguanliDao
    {
        //库存抽取原料出库明细
        public Bean.KucunguanliBean kugunguanli_getdatestartend(string type)
        {
            DB.DBbase dbbase = new DB.DBbase();
            Bean.KucunguanliBean kucunbean = new FWMS.Bean.KucunguanliBean();
            string mingxienddate;
            string kucunguanlithismonthsavedate;
            string sql = "";
            if ("3".Equals(type))
            {
                sql = "select max(end_date)  from chukumingxijiji where type = '3' or type = '4'";
            }
            else
            {
                sql = "select max(end_date)  from chukumingxijiji where type = '" + type + "'";
            }
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    if ("".Equals(dbbase.rows[0, 0].ToString().Trim()))
                    {
                        return null;
                    }
                    else
                    {
                        mingxienddate = dbbase.rows[0, 0].ToString().Trim();
                        kucunguanlithismonthsavedate = kucunguanli_getthissavedate(type);
                        if (int.Parse(mingxienddate) <= int.Parse(kucunguanlithismonthsavedate))
                        {
                            return null;
                        }
                        else
                        {
                            kucunbean.Thismonthsavedate = mingxienddate;
                            kucunbean.Lastmonthsavedate = kucunguanlithismonthsavedate;
                            return kucunbean;
                        }
                    }
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }


        //库存取数据保存时间
        private string kucunguanli_getthissavedate(string type)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string thismonthsavedate;
            string sql = "";

            if ("3".Equals(type))
            {
                sql = "select max(thismonthsavedate)  from kucunguanli where cailiao_type = '3' or cailiao_type = '4'";
            }
            else
            {
                sql = "select max(thismonthsavedate)  from kucunguanli where cailiao_type = '" + type + "'";
            }
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    if ("".Equals(dbbase.rows[0, 0].ToString().Trim()))
                    {
                        return "00010101";
                    }
                    else
                    {
                        thismonthsavedate = dbbase.rows[0, 0].ToString().Trim();
                        return thismonthsavedate;
                    }
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }
        //出入库总流程
        public bool kugunguanli_totalliucheng(System.Windows.Forms.ProgressBar bar, string startdate, string enddate, string type)
        {
            if (kucunguanli_selectchuku(bar, startdate, enddate, type))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //查询时间段入库
        private bool kucunguanli_selectruku(System.Windows.Forms.ProgressBar bar, string startdate, string enddate, string type)
        {
            DB.DBbase dbbase = new DB.DBbase();
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            string sql = "select yuanliaoruku_pinming,ruku_guige,ruku_danwei,ruku_danzhong,ruku_jianshu,ruku_zhongliang from rukuguanli where ruku_type = '" + type + "' and ruku_yanshou between '" + startdate.ToString() + "' and '" + enddate.ToString() + "'";

            bean.Cailiaoresult = (object[,])kucunguanli_select(sql);

            if (bean.Cailiaoresult == null || "".Equals(bean.Cailiaoresult[0, 0].ToString().Trim()))
            {
                return true;
            }
            else
            {
                bean.Thismonthsavedate = enddate;
                bean.Lastmonthsavedate = startdate;
                if (kucunguanli_insertormodify(bar, bean, type))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        //查询时间段出库
        private bool kucunguanli_selectchuku(System.Windows.Forms.ProgressBar bar, string startdate, string enddate, string type)
        {
            DB.DBbase dbbase = new DB.DBbase();
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            string sql = "";
            if ("3".Equals(type))
            {
                sql = "select pinming,guige,danwei,'0','0',sum(shijishiyongzhongliang) from chukumingxijiji where (type = '3' or type = '4')  and  end_date between '" + startdate.ToString() + "' and '" + enddate.ToString() + "' group by pinming,guige,danwei";
            }
            else
            {
                sql = "select pinming,guige,danwei,'0','0',sum(shijishiyongzhongliang) from chukumingxijiji where type = '" + type + "'  and  end_date between '" + startdate.ToString() + "' and '" + enddate.ToString() + "' group by pinming,guige,danwei";
            }

            bean.Cailiaoresult = (object[,])kucunguanli_select(sql);
            if (bean.Cailiaoresult == null || "".Equals(bean.Cailiaoresult[0, 0].ToString().Trim()))
            {
                return false;
            }
            else
            {
                bean.Thismonthsavedate = enddate;
                bean.Lastmonthsavedate = startdate;
                for (int i = 0; i < bean.Cailiaoresult.Length / 6; i++)
                {
                    bar.Value = ((bar.Value++) % 100);
                    bean.Cailiaoresult[i, 3] = (float.Parse(COMMAND.Command.nulltozero(bean.Cailiaoresult[i, 3])) * (-1)).ToString().Trim();
                    bean.Cailiaoresult[i, 4] = (int.Parse(COMMAND.Command.nulltozero(bean.Cailiaoresult[i, 4])) * (-1)).ToString().Trim();
                    bean.Cailiaoresult[i, 5] = (float.Parse(COMMAND.Command.nulltozero(bean.Cailiaoresult[i, 5])) * (-1)).ToString().Trim();
                }
                if (kucunguanli_insertormodify(bar, bean, type))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        //判断是修改还是添加
        private bool kucunguanli_insertormodify(System.Windows.Forms.ProgressBar bar, object bean, string type)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            object[,] result;
            Bean.KucunguanliBean kucunbean = (Bean.KucunguanliBean)bean;
            for (int i = 0; i < kucunbean.Cailiaoresult.Length / 6; i++)
            {
                bar.Value = ((bar.Value++) % 100);
                Bean.KucunguanliBean kucunconditionbean = new FWMS.Bean.KucunguanliBean();
                if (kucunbean.Cailiaoresult[i, 0] == null || "".Equals(kucunbean.Cailiaoresult[i, 0].ToString().Trim()))
                {
                    continue;
                }
                kucunbean.Cailiaoresult[i, 1] = COMMAND.Command.toString(kucunbean.Cailiaoresult[i, 1]);
                //查看库存中是否有时间段内出入库材料
                sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '" + type + "'and pinming = '" + kucunbean.Cailiaoresult[i, 0].ToString().Trim() + "' and guige = '" + kucunbean.Cailiaoresult[i, 1].ToString().Trim() + "'";
                result = (object[,])kucunguanli_select(sql);
                if (result == null || "".Equals(COMMAND.Command.toString(result[0, 0])))
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(kucunbean.Cailiaoresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(kucunbean.Cailiaoresult[i, 1]);
                    kucunconditionbean.Danwei = COMMAND.Command.toString(kucunbean.Cailiaoresult[i, 2]);
                    kucunconditionbean.Gongjin = float.Parse(COMMAND.Command.nulltozero(kucunbean.Cailiaoresult[i, 3]));
                    kucunconditionbean.Jianshu = int.Parse(COMMAND.Command.nulltozero(kucunbean.Cailiaoresult[i, 4]));
                    kucunconditionbean.Zhongliangshiji = float.Parse(COMMAND.Command.nulltozero(kucunbean.Cailiaoresult[i, 5]));
                    kucunconditionbean.Thismonthsavedate = kucunbean.Thismonthsavedate;
                    //添加
                    if (!kucunguanli_insert(kucunconditionbean, type))
                    {
                        return false;//INSERT失败
                    }
                }
                else
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(kucunbean.Cailiaoresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(kucunbean.Cailiaoresult[i, 1]);
                    kucunconditionbean.Lastmonthgongjin = COMMAND.Command.nulltozero(result[0, 0]);
                    kucunconditionbean.Lastmonthjianshu = COMMAND.Command.nulltozero(result[0, 1]);
                    kucunconditionbean.Lastmonthzhongliang = COMMAND.Command.nulltozero(result[0, 2]);
                    kucunconditionbean.Lastmonthsavedate = kucunbean.Lastmonthsavedate;
                    kucunconditionbean.Thismonthgongjin = (Math.Round(float.Parse(COMMAND.Command.nulltozero(kucunbean.Cailiaoresult[i, 3])), 3) + Math.Round(float.Parse(COMMAND.Command.nulltozero(result[0, 0])), 3)).ToString().Trim();
                    kucunconditionbean.Thismonthjianshu = (int.Parse(COMMAND.Command.nulltozero(kucunbean.Cailiaoresult[i, 4])) + int.Parse(COMMAND.Command.nulltozero(result[0, 1]))).ToString().Trim();
                    kucunconditionbean.Thismonthzhongliang = (Math.Round(float.Parse(COMMAND.Command.nulltozero(kucunbean.Cailiaoresult[i, 5])), 3) + Math.Round(float.Parse(COMMAND.Command.nulltozero(result[0, 2])), 3)).ToString().Trim();
                    kucunconditionbean.Thismonthsavedate = kucunbean.Thismonthsavedate;

                    //修改
                    if (!kucunguanli_update(kucunconditionbean, type))
                    {
                        return false;//更新失败
                    }
                }
            }
            return true;
        }
        //库存修改数据 加法
        private bool kucunguanli_update(object condition, string type)
        {
            string sql = "";
            Bean.KucunguanliBean kucunbean = (Bean.KucunguanliBean)condition;
            sql = "update kucunguanli set lastmonthgongjin = '" + kucunbean.Lastmonthgongjin +
                                      "', lastmonthjianshu = '" + kucunbean.Lastmonthjianshu +
                                      "', lastmonthzhongliang = '" + kucunbean.Lastmonthzhongliang +
                                      "',lastmonthsavedate = '" + kucunbean.Lastmonthsavedate +
                                      "', thismonthgongjin = '" + kucunbean.Thismonthgongjin +
                                      "',thismonthjianshu = '" + kucunbean.Thismonthjianshu +
                                      "', thismonthzhongliang = '" + kucunbean.Thismonthzhongliang +
                                      "', thismonthsavedate = '" + kucunbean.Thismonthsavedate +
                                      "'  where pinming = '" + kucunbean.Pinming.ToString().Trim() + "' and guige= '" + kucunbean.Guige.ToString().Trim() + "' and cailiao_type= '" + type + "'";
            if (!kucunguanli_addmodify(sql))
            {
                //更新失败；
                return false;
            }
            return true;
        }
        //库存添加数据
        private bool kucunguanli_insert(object condition, string type)
        {
            DB.DBbase dbbase = new DB.DBbase();

            Bean.KucunguanliBean bean = (Bean.KucunguanliBean)condition;
            string sql = ""; // as user 
            sql = "insert into kucunguanli( pinming, guige,danwei,thismonthgongjin, thismonthjianshu, thismonthzhongliang,thismonthsavedate, cailiao_type ) values('" +
                bean.Pinming + "','" +
                bean.Guige + "','" +
                bean.Danwei + "','" +
                bean.Gongjin + "','" +
                bean.Jianshu + "','" +
                bean.Zhongliangshiji + "','" +
                bean.Thismonthsavedate +
                "','" + type + "' )";
            if (kucunguanli_addmodify(sql))
            {
                return true;//添加成功
            }
            else
            {
                return false;//添加失败
            }
        }
        //执行查询SQL
        private object kucunguanli_select(string sql)
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    return dbbase.rows;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }
        //执行添加修改SQL
        private bool kucunguanli_addmodify(string sql)
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (!dbbase.ExecuteSQL(sql.Trim()))
            {
                return false;
            }
            if (dbbase.command())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //查询数据库中所有的数据 条件类型
        public Bean.KucunguanliBean kucunguanli_selectall(string type)
        {
            string sql = "";
            Bean.KucunguanliBean kucunguanli = new FWMS.Bean.KucunguanliBean();
            sql = "select pinming,danwei,guige,thismonthjianshu,thismonthzhongliang,thismonthsavedate from kucunguanli where cailiao_type = '" + type + "'";
            kucunguanli.Cailiaoresult = (object[,])kucunguanli_select(sql);
            return kucunguanli;
        }
    }
}
